package cn.jbolt.core.db.sql;

import cn.hutool.core.util.ArrayUtil;
import cn.jbolt.core.util.JBoltArrayUtil;
import cn.jbolt.core.util.JBoltDateUtil;
import com.jfinal.kit.StrKit;

import java.io.Serializable;
import java.lang.reflect.Array;
import java.sql.Time;
import java.util.Date;
import java.util.stream.IntStream;

/**
 * 查询条件 用于构造sql语句中的查询条件
 *
 * @ClassName: Condition
 * @author: JFinal学院-小木 QQ：909854136
 * @date: 2019年9月5日
 */
@SuppressWarnings("serial")
public class Condition implements Serializable, Cloneable {

	public static final String EQ = "=";
	public static final String SQL_EXPRESS_EQ = "=";
	public static final String SQL_EXPRESS_NOT_EQ = "!=";
	public static final String SQL_EXPRESS_GT = ">";
	public static final String SQL_EXPRESS_GE = ">=";
	public static final String SQL_EXPRESS_LT = "<";
	public static final String SQL_EXPRESS_LE = "<=";
	public static final String NOT_EQ = "!=";
	public static final String GT = ">";
	public static final String LT = "<";
	public static final String GE = ">=";
	public static final String LE = "<=";
	public static final String WHITESPACE = " ";

	public static final String LIKE = "like";
	public static final String NOTLIKE = "not like";
	public static final String STARTWITH = "startwith";
	public static final String NOT_STARTWITH = "not_startwith";
	public static final String ENDWITH = "endwith";
	public static final String NOT_ENDWITH = "not_endwith";
	public static final String KEY_WHITESPACE = " ";
	public static final String KEY_PERCENT = "%";
	public static final String KEY_SINGLE_QUOTATION_MARK = "'";
	public static final String KEY_IS_NULL = " is null";
	public static final String KEY_IS_NOT_NULL = " is not null";
	public static final String KEY_INSTR = " instr";
	public static final String KEY_EXISTS = "exists";
	public static final String KEY_NOT_EXISTS = "not exists";

	public static final String OR = "or";
	public static final String AND = "and";

	public static final String BRACKET_LEFT = "(";
	public static final String BRACKET_RIGHT = ")";

	public static final int TYPE_COMPARE = 1;// 普通比较
	public static final int TYPE_IN = 2;// in
	public static final int TYPE_NOT_IN = 3;// not in
	public static final int TYPE_LINK = 4;// 普通字符连接
	public static final int TYPE_FINDINSET = 5;// findinset
	public static final int TYPE_ISNULL = 6;// 比较 is null
	public static final int TYPE_ISNOTNULL = 7;// 比较 is not null
	public static final int TYPE_INSTR = 8;// instr
	public static final int TYPE_NOT_INSTR = 9;// not instr
	public static final int TYPE_EXISTS = 10;// exists
	public static final int TYPE_NOT_EXISTS = 11;// not exists
	public static final int TYPE_NOT_FINDINSET = 12;// not findinset

	private String key;//
	private boolean keyIsTableColumn;// key是否为数据库字段
	private Object value1;// 左边
	private Object value2;// 右边
	private String likeValue;
	private Object[] inValues;
	private String sqlStr;
	private String compareState;
	private int type;
	private String dbType;
	private boolean findInSetValueIsTableColumn;

	public Condition() {

	}

	public Condition(String key, Object value, String compareState) {
		setKey(key);
		this.compareState = compareState;
		this.type = TYPE_COMPARE;
		this.value1 = processValue(value);
	}

	public Condition(String key, Object value1, Object value2, String compareState) {
		setKey(key);
		this.compareState = compareState;
		this.type = TYPE_COMPARE;
		this.value1 = processValue(value1);
		this.value2 = processValue(value2);
	}

	private Object processValue(Object value) {
		//判断compareState是like的时候 如果value是字符串类型 并且包含百分号 就给百分号转义
		if ((compareState.equals(LIKE) || compareState.equals(NOTLIKE)) && value instanceof String) {
			String str = value.toString();
			if (str.contains(KEY_PERCENT)) {
				str = str.replace(KEY_PERCENT, "\\" + KEY_PERCENT);
			}
			return str;
		} else {
			return value;
		}
	}

	/**
	 * 转换
	 *
	 * @param dbType
	 * @return
	 */
	public String toSql(String dbType) {
		return toSql(dbType, false);
	}

	/**
	 * 转换
	 *
	 * @param dbType
	 * @param prepared
	 * @return
	 */
	public String toSql(String dbType, boolean prepared) {
		this.dbType = dbType;
		String sql = null;
		switch (type) {
			case TYPE_COMPARE:
				sql = processTypeCompareSql(prepared);
				break;
			case TYPE_LINK:
				sql = KEY_WHITESPACE + value1 + KEY_WHITESPACE;
				break;
			case TYPE_FINDINSET:
				sql = processFindInSetSql();
				break;
			case TYPE_NOT_FINDINSET:
				sql = processNotFindInSetSql();
				break;
			case TYPE_ISNULL:
				sql = processIsNullSql();
				break;
			case TYPE_ISNOTNULL:
				sql = processIsNotNullSql();
				break;
			case TYPE_INSTR:
				sql = processInstr();
				break;
			case TYPE_NOT_INSTR:
				sql = processNotInstr();
				break;
			case TYPE_IN:
				sql = processIn(sql);
				break;
			case TYPE_NOT_IN:
				sql = processIn(sql);
				break;
			case TYPE_EXISTS:
				sql = processExist(true);
				break;
			case TYPE_NOT_EXISTS:
				sql = processExist(false);
				break;
			default:
				break;
		}
//		if(type==TYPE_COMPARE){
//			sql = processTypeCompareSql(prepared);
//		}else if(type==TYPE_LINK){
//			sql=KEY_WHITESPACE+value1+KEY_WHITESPACE;
//		}else if(type==TYPE_COMPARE_FINDINSET){
//			sql=processFindInSetSql();
//		}else if(type==TYPE_COMPARE_ISNULL){
//			sql=processIsNullSql();
//		}else if(type==TYPE_COMPARE_ISNOTNULL){
//			sql=processIsNotNullSql();
//		}else if(type==TYPE_COMPARE_INSTR){
//			sql=processInstr();
//		}else if(type==TYPE_COMPARE_NOT_INSTR){
//			sql=processNotInstr();
//		}else if(type==TYPE_COMPARE_IN||type==TYPE_COMPARE_NOT_IN){
//			sql = processIn(sql);
//		}
		return sql;
	}

	private String processExist(boolean exists) {
		if (StrKit.isBlank(sqlStr)) {
			return null;
		}
		return (exists ? KEY_EXISTS : KEY_NOT_EXISTS) + "(" + sqlStr + ") ";
	}

	private String processIn(String sql) {
		String keywords = type == TYPE_IN ? "in" : "not in";
		if (StrKit.notBlank(sqlStr)) {
			sql = KEY_WHITESPACE + key + KEY_WHITESPACE + keywords + "(" + sqlStr + ") ";
		} else {
			sql = KEY_WHITESPACE + key + KEY_WHITESPACE + keywords + "(";
			Object inv;
			Object[] inDatas;
			int len = inValues.length;
			for (int i = 0; i < len; i++) {
				inv = inValues[i];
				if (inv instanceof String) {
					sql += safeValue(inv.toString());
				} else if (inv instanceof SqlExpress) {
					sql += inv;
				} else {
					if(inv.getClass().isArray()){
						int newLen = Array.getLength(inv);
						if(newLen>0){
							if(newLen == 1){
								sql += "'" + Array.get(inv, 0) + "'";
							}else{
								Object finalInv = inv;
								inDatas = IntStream.range(0, newLen)
										.mapToObj(x -> Array.get(finalInv, x))
										.toArray();
								sql += ArrayUtil.join(inDatas,",","'","'");
							}
						}
					}else{
						sql += "'" + inv + "'";
					}
				}

				if (i != inValues.length - 1) {
					sql += ",";
				}
			}
			sql += ")" + KEY_WHITESPACE;
		}
		return sql;
	}

	private String processTypeCompareSql(boolean prepared) {
		String sql;
		// 判断处理特殊查询条件
		switch (compareState) {
			case LIKE:
				sql = processLikeConditions(prepared, false);
				break;
			case STARTWITH:
				sql = processStartWithConditions(prepared, false);
				break;
			case ENDWITH:
				sql = processEndWithConditions(prepared, false);
				break;
			case NOTLIKE:
				sql = processLikeConditions(prepared, true);
				break;
			case NOT_STARTWITH:
				sql = processStartWithConditions(prepared, true);
				break;
			case NOT_ENDWITH:
				sql = processEndWithConditions(prepared, true);
				break;
			default:
				if (value1 instanceof String) {
					sql = key + compareState + (prepared ? "?" : (value1.equals("?") ? "?" : safeValue(value1.toString())));
				} else if (value1 instanceof Boolean) {
					sql = key + compareState + (prepared ? "?" : SqlUtil.boolToChar((Boolean) value1));
				} else if (value1 instanceof Date) {
					sql = key + compareState + (prepared ? "?" : safeValue(JBoltDateUtil.format((Date)value1,JBoltDateUtil.YMDHMS)));
				} else if (value1 instanceof Time) {
					sql = key + compareState + (prepared ? "?" : safeValue(JBoltDateUtil.getTime((Time)value1)));
				} else {
					sql = key + compareState + (prepared ? "?" : value1);
				}
				break;
		}
		return sql;
	}

	private String safeValue(String value) {
		if (value.indexOf("'") != -1) {
			value = value.replace("'", "''");
		}
		return "'" + value + "'";
	}

	private String safeValueForLike(String value) {
		if (value.indexOf("'") != -1) {
			value = value.replace("'", "''");
		}
		return value;
	}

	/**
	 * 处理IS NULL 语句
	 *
	 * @return
	 */
	private String processIsNullSql() {
		return value1 + KEY_IS_NULL;
	}

	/**
	 * 处理IS NOT NULL 语句
	 *
	 * @return
	 */
	private String processIsNotNullSql() {
		return value1 + KEY_IS_NOT_NULL;
	}

	/**
	 * 处理instr() 语句
	 *
	 * @return
	 */
	private String processInstr() {
		String sql = null;
		switch (dbType) {
			case DBType.MYSQL:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") > 0 ";
				break;
			case DBType.POSTGRESQL:
				sql = " strpos(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") > 0 ";
				break;
			case DBType.ORACLE:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") > 0 ";
				break;
			case DBType.SQLSERVER:
				sql = " charindex(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") > 0 ";
				break;
			case DBType.DM:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") > 0 ";
				break;
		}
		return sql;
	}

	/**
	 * 处理notinstr() 语句
	 *
	 * @return
	 */
	private String processNotInstr() {
		String sql = null;
		switch (dbType) {
			case DBType.MYSQL:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") = 0 ";
				break;
			case DBType.POSTGRESQL:
				sql = " strpos(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") = 0 ";
				break;
			case DBType.ORACLE:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") = 0 ";
				break;
			case DBType.SQLSERVER:
				sql = " charindex(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") = 0 ";
				break;
			case DBType.DM:
				sql = " instr(" + (keyIsTableColumn ? key : (safeValue(key))) + "," + safeValue(value1.toString())
						+ ") = 0 ";
				break;
		}
		return sql;
	}

	/**
	 * 处理FIND_IN_SET 语句
	 *
	 * @return
	 */
	private String processFindInSetSql() {
		return " find_in_set(" + key + ","
				+ (findInSetValueIsTableColumn ? value1 : (safeValue(value1.toString()))) + ")>0 ";
	}

	/**
	 * 处理NOT FIND_IN_SET 语句
	 *
	 * @return
	 */
	private String processNotFindInSetSql() {
		return " find_in_set(" + key + ","
				+ (findInSetValueIsTableColumn ? value1 : (safeValue(value1.toString()))) + ")=0 ";
	}


	/**
	 * 处理 like
	 *
	 * @param prepared
	 * @return
	 */
	private String processLikeConditions(boolean prepared, boolean not) {
//		processLikeNot(not);
		String selfCompareState = null;
		if (not) {
			selfCompareState = NOTLIKE;
		} else {
			selfCompareState = LIKE;
		}
		// 根据数据库类型判断
		if (prepared) {
			switch (dbType) {
				case DBType.MYSQL:
					likeValue = "concat('%',?,'%')";
					break;
				case DBType.POSTGRESQL:
					likeValue = "'%'|| ? ||'%'";
					break;
				case DBType.ORACLE:
					likeValue = "'%'|| ? ||'%'";
					break;
				case DBType.SQLSERVER:
					likeValue = "'%' + ? + '%'";
					break;
				case DBType.DM:
					likeValue = "concat('%',?,'%')";
					break;
			}
		} else {
			likeValue = "'%" + safeValueForLike(value1.toString()) + "%'";
		}
		return KEY_WHITESPACE + key + KEY_WHITESPACE + selfCompareState + KEY_WHITESPACE + likeValue + KEY_WHITESPACE;
	}

	/**
	 * 处理 startWith
	 *
	 * @param prepared
	 * @return
	 */
	private String processStartWithConditions(boolean prepared, boolean not) {
//		processLikeNot(not);
		String selfCompareState = null;
		if (not) {
			selfCompareState = NOTLIKE;
		} else {
			selfCompareState = LIKE;
		}
		// 根据数据库类型判断
		if (prepared) {
			switch (dbType) {
				case DBType.MYSQL:
					likeValue = "concat(?,'%')";
					break;
				case DBType.POSTGRESQL:
					likeValue = " ? ||'%'";
					break;
				case DBType.ORACLE:
					likeValue = " ? ||'%'";
					break;
				case DBType.SQLSERVER:
					likeValue = " ? + '%'";
					break;
				case DBType.DM:
					likeValue = "concat(?,'%')";
					break;
			}
		} else {
			likeValue = "'" + safeValueForLike(value1.toString()) + "%'";
		}
		return KEY_WHITESPACE + key + KEY_WHITESPACE + selfCompareState + KEY_WHITESPACE + likeValue + KEY_WHITESPACE;
	}

	/**
	 * 处理 endWith
	 *
	 * @param prepared
	 * @return
	 */
	private String processEndWithConditions(boolean prepared, boolean not) {
//		processLikeNot(not);
		String selfCompareState = null;
		if (not) {
			selfCompareState = NOTLIKE;
		} else {
			selfCompareState = LIKE;
		}
		// 根据数据库类型判断
		if (prepared) {
			switch (dbType) {
				case DBType.MYSQL:
					likeValue = "concat('%',?)";
					break;
				case DBType.POSTGRESQL:
					likeValue = "'%'|| ?";
					break;
				case DBType.ORACLE:
					likeValue = "'%'|| ?";
					break;
				case DBType.SQLSERVER:
					likeValue = "'%'+ ?";
					break;
				case DBType.DM:
					likeValue = "concat('%',?)";
					break;
			}
		} else {
			likeValue = "'%" + safeValueForLike(value1.toString()) + "'";
		}
		return KEY_WHITESPACE + key + KEY_WHITESPACE + selfCompareState + KEY_WHITESPACE + likeValue + KEY_WHITESPACE;
	}

	/*
	 * private void processLikeNot(boolean not) { if(not) { compareState=NOTLIKE;
	 * }else { compareState=LIKE; } }
	 */
	public Object getValue1() {
		return value1;
	}

	public void setValue1(Object value1) {
		this.value1 = value1;
	}

	public Object getValue2() {
		return value2;
	}

	public void setValue2(Object value2) {
		this.value2 = value2;
	}

	public String getCompareState() {
		return compareState;
	}

	public void setCompareState(String compareState) {
		this.compareState = compareState;
	}

	/**
	 * column is null
	 *
	 * @return
	 */
	public Condition isNull(String columnName) {
		this.value1 = columnName;
		this.type = TYPE_ISNULL;
		return this;
	}

	/**
	 * column is not null
	 *
	 * @return
	 */
	public Condition isNotNull(String columnName) {
		this.value1 = columnName;
		this.type = TYPE_ISNOTNULL;
		return this;
	}

	/**
	 * 左括号
	 *
	 * @return
	 */
	public Condition bracketLeft() {
		this.value1 = BRACKET_LEFT;
		this.type = TYPE_LINK;
		return this;
	}

	/**
	 * 右括号
	 *
	 * @return
	 */
	public Condition bracketRight() {
		this.value1 = BRACKET_RIGHT;
		this.type = TYPE_LINK;
		return this;
	}

	public Condition or() {
		this.value1 = OR;
		this.type = TYPE_LINK;
		return this;
	}

	public Condition and() {
		this.value1 = AND;
		this.type = TYPE_LINK;
		return this;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}

	public int getType() {
		return type;
	}

	public void setType(int type) {
		this.type = type;
	}

	public Condition findInSet(Object key, String value, boolean valueIsTableColumn) {
		if(key instanceof SqlExpress){
			setKey(key.toString());
		}else{
			setKey(safeValue(key.toString()));
		}
		this.type = TYPE_FINDINSET;
		this.value1 = value;
		this.findInSetValueIsTableColumn = valueIsTableColumn;
		return this;
	}

	public Condition notFindInSet(Object key, String value, boolean valueIsTableColumn) {
		if(key instanceof SqlExpress){
			setKey(key.toString());
		}else{
			setKey(safeValue(key.toString()));
		}
		this.type = TYPE_NOT_FINDINSET;
		this.value1 = value;
		this.findInSetValueIsTableColumn = valueIsTableColumn;
		return this;
	}

	public Condition in(String key, Object... inValues) {
		setKey(key);
		this.type = TYPE_IN;
		this.inValues = inValues;
		return this;
	}
	/**
	 * 自动装箱处理
	 * @param inValues
	 * @return
	 */
	private Object[] processInValues(Object[] inValues) {

		if(inValues!=null && inValues.length>0){
			Class clazz = inValues[0].getClass();
			if(clazz.isArray() && clazz.isPrimitive()){
				Object array = inValues[0];
				return IntStream.range(0, Array.getLength(array))
						.mapToObj(i -> Array.get(array, i))
						.toArray();
			}
		}
		return inValues;
	}

	public Condition notIn(String key, Object... notInValues) {
		setKey(key);
		this.type = TYPE_NOT_IN;
		this.inValues = notInValues;
		return this;
	}

	public Condition in(String key, String inValues) {
		setKey(key);
		this.type = TYPE_IN;
		this.inValues = JBoltArrayUtil.from(inValues, ",");
		return this;
	}

	public Condition instr(String str, String substr, boolean strIsTableColumn) {
		setKey(str);
		this.value1 = substr;
		this.keyIsTableColumn = strIsTableColumn;
		this.type = TYPE_INSTR;
		return this;
	}

	public Condition notInstr(String str, String substr, boolean strIsTableColumn) {
		setKey(str);
		this.value1 = substr;
		this.keyIsTableColumn = strIsTableColumn;
		this.type = TYPE_NOT_INSTR;
		return this;
	}

	public Condition notIn(String key, String inValues) {
		setKey(key);
		this.type = TYPE_NOT_IN;
		this.inValues = JBoltArrayUtil.from(inValues, ",");
		return this;
	}

	public Condition inSql(String key, String inSql) {
		setKey(key);
		this.type = TYPE_IN;
		this.sqlStr = inSql;
		return this;
	}

	public Condition notInSql(String key, String inSql) {
		setKey(key);
		this.type = TYPE_NOT_IN;
		this.sqlStr = inSql;
		return this;
	}

	public Condition exists(String sql) {
		this.type = TYPE_EXISTS;
		this.sqlStr = sql;
		return this;
	}

	public Condition notExists(String sql) {
		this.type = TYPE_NOT_EXISTS;
		this.sqlStr = sql;
		return this;
	}

	public Object[] getInValues() {
		return inValues;
	}

	public void setInValues(Object[] inValues) {
		this.inValues = inValues;
	}

	public String getInSql() {
		return sqlStr;
	}

	public void setInSql(String inSql) {
		this.sqlStr = inSql;
	}

	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

}
